/**
 * EditTakenCourseInfoDAO class.
 */
package pits.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import pits.dto.EditTakenCourseInfoDTO;
import pits.form.EditTakenCourseForm;
import pits.util.Constants;

public class EditTakenCourseInfoDAO {

	public EditTakenCourseInfoDTO getData(String courseID, String empID) {
		EditTakenCourseInfoDTO result = new EditTakenCourseInfoDTO();

		Connection conn = null;
		String query = null;
		PreparedStatement sqlStatement = null;
		ResultSet courseResultSet = null;
		ResultSet departResultSet = null;

		try {
			conn = pits.util.Connection.getConnection();

			query = "SELECT * FROM course WHERE CourseID=" + courseID + " AND isDeleted=0";

			sqlStatement = conn.prepareStatement(query);
			courseResultSet = sqlStatement.executeQuery();
			courseResultSet.next();

			result.setCourseName(courseResultSet.getString("Name"));
			result.setDescription(courseResultSet.getString("Description"));
			result.setPeriod(courseResultSet.getString("Period"));
			result.setDateIssused(courseResultSet.getString("DateIssused"));

			query = "SELECT * FROM department WHERE DepartID=" + courseResultSet.getInt("DepartID")
			        + " AND isDeleted=0";
			conn = pits.util.Connection.getConnection();
			sqlStatement = conn.prepareStatement(query);
			departResultSet = sqlStatement.executeQuery();
			departResultSet.next();

			result.setDepartName(departResultSet.getString("DepartName"));

			query = "SELECT * FROM courseexp" + " WHERE CourseID=" + courseID + " AND EmpID=" + empID
			        + " AND isDeleted=0";
			conn = pits.util.Connection.getConnection();
			sqlStatement = conn.prepareStatement(query);
			courseResultSet = sqlStatement.executeQuery();
			courseResultSet.next();

			result.setGrade(courseResultSet.getString("Grade"));

		} catch (Exception sqlEx) {
			new pits.util.Logger().writeln(sqlEx.toString());
		} finally {
			try {
				departResultSet.close();
				courseResultSet.close();
				sqlStatement.close();
				conn.close();
			} catch (SQLException sqlEx) {
				new pits.util.Logger().writeln(sqlEx.toString());
			}
		}

		return result;
	}

	/**
	 * Update new information to database
	 */
	public void update(EditTakenCourseForm formTakenCourse) {
		Connection connection = null;
		String courseQuery = Constants.BLANK_VALUE;
		String courseexpQuery = Constants.BLANK_VALUE;
		PreparedStatement sqlStatement = null;

		try {
			courseQuery = "UPDATE course" + " SET DateIssused='" + formTakenCourse.getDateIssused() + "'"
			        + " WHERE CourseID=" + formTakenCourse.getCourseID() + " AND isDeleted=0";

			// new pits.util.Logger().writeln(courseQuery);

			courseexpQuery = "UPDATE courseexp" + " SET Grade='" + formTakenCourse.getGrade() + "'"
			        + " WHERE CourseID=" + formTakenCourse.getCourseID() + " AND EmpID=" + formTakenCourse.getEmpID()
			        + " AND isDeleted=0";

			// new pits.util.Logger().writeln(courseexpQuery);

			connection = pits.util.Connection.getConnection();

			sqlStatement = connection.prepareStatement(courseQuery);
			sqlStatement.executeUpdate();

			sqlStatement = connection.prepareStatement(courseexpQuery);
			sqlStatement.executeUpdate();
		} catch (Exception sqlEx) {
			new pits.util.Logger().writeln("SimpleLoginDAO.getUser " + sqlEx);
		} finally {
			try {
				sqlStatement.close();
				connection.close();
			} catch (SQLException sqlEx) {
				new pits.util.Logger().writeln("SimpleLoginDAO.getUser " + sqlEx);
			}
		}
	}
}
